<?php
    class User extends CActiveRecord
    {
        public static function model($className = __CLASS__) {
            return parent::model ( $className );
        }

        public function tableName() {                  
            return 'c_user';
        }

        /* Lấy game offline no sms mới nhất */
        public function getLastestGame()
        {
            $sql = "SELECT id, title, alias FROM c_game WHERE status=1 AND isOnline=0 AND isSms=0 ORDER BY create_date DESC LIMIT 1";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();

            return $rows;
        }

        /* Lấy game online mới nhất */
        public function getLastestGameOnline()
        {
            $sql = "SELECT id, title, alias FROM c_game WHERE status=1 AND isOnline=1 ORDER BY download DESC LIMIT 1";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();

            return $rows;
        }

        /* Lấy list ứng dụng */
        public function getListApp()
        {
            $sql = "SELECT * FROM c_app WHERE type NOT IN (1,6,7)  ORDER BY type";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();     
            for($i=0;$i<count($rows);$i++){
                $rows[$i]["introtext"] = LoadConfig::$apps_data[$rows[$i]["id"]]["introtext"];
                $rows[$i]["description"] = LoadConfig::$apps_data[$rows[$i]["id"]]["description"];
            }   
            return $rows;   
        }

        /* Thông tin về ứng dụng */
        public function getInfoApp($appId)
        {
            $sql = "SELECT * FROM c_app WHERE id=" . $appId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();  
            return $rows;   
        }

        /* thêm mới share trang chi tiết application */
        public function InsertShare($data)
        {
            $sql = "INSERT INTO c_share SET 
            userId=:userId
            , username=:username
            , content=:content
            , create_date=:create_date
            ";
            $command = Yii::app()->db->createCommand($sql);
            $command->bindValues($data);
            $result = $command->execute();
            return $result;
        }
        public function insertNewShare($data){
            $sql = "INSERT INTO c_share SET 
            userId=:userId
            , username=:username
            , content=:content
            , dataId=:dataId
            , type=:type
            , create_date=:create_date
            ";
            $command = Yii::app()->db->createCommand($sql);
            $command->bindValues($data);
            $result = $command->execute();
            return $result;
        }

        /* Lấy các chia sẻ */
        public function getShare($currentPage, $numberRecordPerPage,$dataId=0,$type=0)
        {
            $startRecord = ($currentPage - 1) * $numberRecordPerPage; 
            $sql = "SELECT * FROM c_share WHERE dataId = ".intval($dataId)." AND type=".intval($type)." ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        
            return $rows;
        }

        public function getTotalShare($dataId=0,$type=0)
        {
            $sql = "SELECT count(id) FROM c_share WHERE dataId = ".intval($dataId)." AND type=".intval($type)."";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        
            return $rows["count(id)"];
        }

        /* Thêm mới quảng cáo thành viên đưa lên */
        public function InsertMyAdv($data)
        {
            $sql = "INSERT INTO c_my_adv SET 
            userId=:userId
            , username=:username
            , type=:type
            , title=:title
            , alias=:alias
            , picture=:picture            
            , create_date=:create_date
            ";
            $command = Yii::app()->db->createCommand($sql);
            $command->bindValues($data);
            $result = $command->execute();
            $id = Yii::app()->db->getLastInsertID();

            return $id;
        }

        /* THông tin về quảng cáo của thành viên */
        public function getInfoMyAdv($advId)
        {
            $sql = "SELECT * FROM c_my_adv WHERE id=" . $advId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        
            return $rows;   
        }

        /* Lấy tất cả quảng cáo do admin tạo */
        public function getBoxAdv()
        {
            $sql = "SELECT title, picture, link, create_date FROM c_box_adv WHERE status=1 ORDER BY create_date DESC";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        
            return $rows;   
        }

        /* lấy danh sách quảng cáo của thành vien này */
        public function getListAdv($userId)
        {
            $sql = "SELECT * FROM c_my_adv WHERE userId=" . $userId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        
            return $rows;
        }

        /* Xóa quảng cáo của tôi */
        public function deleteMyAdv($id)
        {
            $sql = "DELETE FROM c_my_adv WHERE id=" . $id;
            $command = Yii::app()->db->createCommand($sql);
            $result = $command->execute();        
            return $result;
        }

        /* Cập nhật số lượt download application */
        public function UpdateDownloadApp($appId)
        {
            $sql = "UPDATE c_app SET download=(download+1) WHERE id=" . $appId;
            $command = Yii::app()->db->createCommand($sql);
            $result = $command->execute();        
            return $result;   
        }

        /* Lấy thông tin về quảng cáo của thành viên */
        public function getMyAdv($id)
        {
            $sql = "SELECT * FROM c_my_adv WHERE id=" . $id;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        

            return $rows;
        }

        /* cập nhật số lượt download quảng cáo của thành viên */
        public function UpdateDownloadMyAdv($myAdvId)
        {
            $sql = "UPDATE c_my_adv SET download=(download+1) WHERE id=" . $myAdvId;
            $command = Yii::app()->db->createCommand($sql);
            $result = $command->execute();        
            return $result;   
        }     

        /* Lấy tổng số thành viên */
        public function getTotalUser()
        {
            $sql = "SELECT count(id) FROM c_user";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        

            return $rows["count(id)"];
        }

        /* Lấy các thành viên sinh nhật hôm nay */
        public function getUserBirthdayToday()
        {
            $today = date("d-m");                                 
            $sql = "SELECT username FROM c_user WHERE day_birthday='" . $today . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        

            return $rows;   
        }

        public function getUserId($refCode)
        {
            $sql = "SELECT id, username FROM c_user WHERE refCode='" . mysql_escape_string($refCode) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }

        public function getUserInfo($userId)
        {
            $sql = "SELECT * FROM c_user WHERE id=" . $userId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }

        /* Thêm xác nhận thanh toán */
        public function InsertConfirmPayment($data)
        {
            $sql = "INSERT INTO c_payment_confirm SET 
            userId=:userId
            , money=:money
            , full_name=:full_name
            , birthday=:birthday
            , address=:address
            , identity_number=:identity_number
            , mobile=:mobile
            , email=:email
            , wap=:wap
            , introtext=:introtext
            , type=:type
            , account_owner=:account_owner
            , account_number=:account_number
            , account_bank=:account_bank
            , account_branch=:account_branch
            , mobile_receive=:mobile_receive
            , month=:month
            , year=:year
            , create_date=:create_date            
            ";

            $command = Yii::app()->db->createCommand($sql);
            $command->bindValues($data);
            $result = $command->execute();
            return $result;
        }

        /* Lịch sử thanh toán */
        public function getPaymentHistory($userId)
        {
            $sql = "SELECT id, money, month, year, create_date FROM c_payment_confirm WHERE userId=" . $userId . " ORDER BY create_date DESC";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            return $rows;

        }

        public function getDetailPayment($id, $userId)
        {
            $sql = "SELECT * FROM c_payment_confirm WHERE id=" . $id . " AND userId=" . $userId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;   
        }

        public function getTotalReceive($userId)
        {
            $sql = "SELECT sum(money) FROM c_payment_confirm WHERE userId=" . $userId;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows["sum(money)"];
        }

        public function checkPaymentConfirm($userId)
        {
            $month = date("m");
            $year = date("Y");
            $sql = "SELECT count(id) FROM c_payment_confirm WHERE userId=" . $userId . " AND month=" . $month . " AND year=" . $year;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows["count(id)"];
        }

        public function getDataByUsername($username){
            $sql = "SELECT id,username FROM c_user WHERE username = '".mysql_escape_string($username)."'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }

        public function getCpCode($username)
        {
            $sql = "SELECT code FROM c_vankiem WHERE username='" . mysql_escape_string($username) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }

        public function getRandomAdv()
        {
            $sql = "SELECT title, picture, link, create_date FROM c_box_adv WHERE status=1 ORDER BY RAND() LIMIT 10";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        
            return $rows;
        }

        public function getRandomAdvLimit1()
        {
            $sql = "SELECT title, picture, link, create_date FROM c_box_adv WHERE status=1 ORDER BY RAND() LIMIT 1";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        
            return $rows;
        }
        public function getRefCode($userId)
        {
            $sql = "SELECT refCode  FROM c_user WHERE id='" . mysql_escape_string($userId) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows;
        }
        
          public function checkPass($userId, $password)
    {        
        $sql = "SELECT id, email FROM c_user WHERE id=" . $userId . " AND password='" . $password . "'";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }
    
     public function checkMobile($mobile)
        {
            $sql = "SELECT count(id) FROM c_user WHERE mobile='" . mysql_escape_string($mobile) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();
            return $rows["count(id)"];
        }
     
      public function checkEmail($email)
    {
        $sql = "SELECT count(id) FROM c_user WHERE email='" . mysql_escape_string($email) . "'";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows["count(id)"];
    }
    
     public function checkUsername($username)
    {
        $sql = "SELECT count(id) FROM c_user WHERE username='" . mysql_escape_string($username) . "'";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows["count(id)"];
    }
}  
?>